﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;

#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class PicNodeProvider : PhotoGallery.Core.Providers.DALProvider.IPicNodeProvider
    {
        #region Sql
        private static readonly string SqlSelectPicNode = "SELECT [Id], [Status],[IsFile],[Root],[Path],[Name] FROM [PicNode] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertPicNode = "INSERT INTO [PicNode]([Status],[IsFile],[Root],[Path],[Name]) values(@Status,@IsFile,@Root,@Path,@Name);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdatePicNode = "UPDATE [PicNode] SET [Status] = @Status,[IsFile] = @IsFile,[Root] = @Root,[Path] = @Path ,[Name] = @Name WHERE  [Id]=@Id;";

        private static readonly string SqlDeletePicNode = "DELETE [PicNode] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllPicNode = "SELECT [Id], [Status],[IsFile],[Root],[Path],[Name] FROM [PicNode];";

        private static readonly string SqlGetSubs = "select distinct child.id,child.Status,child.IsFile,child.Root,child.Path,child.name from picnode parent inner join picnode child on parent.status=0 and child.status=0 and parent.id=@nodeid and child.id!=@nodeid  and  child.root=parent.root+'.'+rtrim(ltrim(str(child.id)))";

        private static readonly string SqlGetRoot = "select top 1 picnode.id,picnode.Status,picnode.IsFile,picnode.Root,picnode.Path,picnode.name from picnode where status=0 order by len(root)";

        private static readonly string SqlGetRoots = "select distinct picnode.id,picnode.Status,picnode.IsFile,picnode.Root,picnode.Path,picnode.name from picnode where len(root)=1 and status=0";


        private static readonly string SqlInsertNew = "INSERT INTO [PicNode]([Status],[IsFile],[Root],[Path],[Name]) values(@Status,@IsFile,@Root,@Path,@Name); declare @id int ;set @id= SCOPE_IDENTITY(); UPDATE PICNODE SET ROOT=ROOT+'.'+ltrim(id) where id=@id";


        private static readonly string SqlDeleteDesc = "update picnode set status =1 where id in (select id from picnode where root like '%'+'.'+rtrim(ltrim(str(@id)))+'.'+'%' " +
" or (root like '%'+rtrim(ltrim(str(@id)))+'.'+'%' and root like rtrim(ltrim(str(@id)))+'%' )) or id=@id ";



        private static readonly string SqlSearch = " select distinct child.[Id], child.[Status],child.[IsFile],child.[Root],child.[Path],child.[Name]  , folderdesc.[Id], folderdesc.[EventInfo],folderdesc.[StartDate],folderdesc.[EndDate],folderdesc.[KeyWord],folderdesc.[Photographer],folderdesc.[Leader],folderdesc.[OtherLeader],folderdesc.[Guest],folderdesc.[InnerLocation],folderdesc.[OuterLocation],folderdesc.[EventDesc]  from picnode parent inner join picnode child  on ( parent.id=@id and child.root like parent.root+ '%' or @id is null ) and ( parent.status=@status  or @status is null ) and ( child.status=@status  or @status is null )  and  ( child.name like '%'+@foldername+'%' or @foldername is null)  left join  folderdesc   on child.id= folderdesc.id  where   ( folderdesc.keyword like '%'+@keyword+'%' or @keyword is null)  and ( folderdesc.photographer like '%'+@photographer+'%' or @photographer is null)  and ( folderdesc.leader like '%'+@leader+'%' or @leader is null)  and ( folderdesc.otherleader like '%'+@otherleader+'%' or @otherleader is null )  and ( folderdesc.guest like '%'+@guest+'%' or @guest is null)  and  ( folderdesc.innerlocation like '%'+@location+'%'   or folderdesc.outerlocation like '%'+@location+'%' or  @location is null)  and (  folderdesc.eventinfo= @eventinfo or @eventinfo is null )  and ( folderdesc.eventdesc like '%'+@eventdesc+'%' or @eventdesc is null) order by child.root ";

        private static readonly string SqlFindByPath = " SELECT [Id], [Status],[IsFile],[Root],[Path],[Name] FROM [PicNode] WHERE  [Path]=@Path and [Status]=@Status; ";




        #endregion

        #region Procedures
        /*

-- Begin of PicNode
IF OBJECT_ID(N'[USP_PicNode_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_PicNode_Delete_By_Id]

IF OBJECT_ID(N'[USP_PicNode_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_PicNode_Select_By_Id]

IF OBJECT_ID(N'[USP_PicNode_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_PicNode_Insert]

IF OBJECT_ID(N'[USP_PicNode_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_PicNode_Update_By_Id]

IF OBJECT_ID(N'[USP_PicNode_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_PicNode_SelectAll]

GO

--region [USP_PicNode_Delete_By_Id]
CREATE PROCEDURE [USP_PicNode_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [PicNode] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_PicNode_Select_By_Id]
CREATE PROCEDURE [USP_PicNode_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Status],[IsFile],[Root],[Path] FROM [PicNode] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_PicNode_Insert]
CREATE PROCEDURE [USP_PicNode_Insert]
	@Status	int,
	@IsFile	bit,
	@Root	nvarchar(100),
	@Path	nvarchar(500)
AS
SET NOCOUNT ON
INSERT INTO [PicNode]([Status],[IsFile],[Root],[Path]) values(@Status,@IsFile,@Root,@Path);SELECT SCOPE_IDENTITY();
--endregion
GO

--region [USP_PicNode_Update_By_Id]
CREATE PROCEDURE [USP_PicNode_Update_By_Id]
	@Status	int,
	@IsFile	bit,
	@Root	nvarchar(100),
	@Path	nvarchar(500),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [PicNode] SET [Status] = @Status,[IsFile] = @IsFile,[Root] = @Root,[Path] = @Path WHERE  [Id]=@Id;
--endregion
GO

--region [USP_PicNode_SelectAll]
CREATE PROCEDURE [USP_PicNode_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Status],[IsFile],[Root],[Path] FROM [PicNode];
--endregion
GO


-- End of PicNode
*/
        #endregion

        #region IPicNodeProvider Members
        public Core.Business.PicNode Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectPicNode);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.PicNode picNode = new Core.Business.PicNode();

                if (!reader.IsDBNull(0)) picNode.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) picNode.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) picNode.IsFile = reader.GetBoolean(2);
                if (!reader.IsDBNull(3)) picNode.Root = reader.GetString(3);
                if (!reader.IsDBNull(4)) picNode.Path = reader.GetString(4);
                if (!reader.IsDBNull(5)) picNode.Name = reader.GetString(5);

                reader.Close();
                return picNode;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.PicNode picNode)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, picNode.Status);
            sql.AddParameter("@IsFile", SqlDbType.Bit, picNode.IsFile);
            sql.AddParameter("@Root", SqlDbType.NVarChar, picNode.Root);
            sql.AddParameter("@Path", SqlDbType.NVarChar, picNode.Path);
            sql.AddParameter("@Name", SqlDbType.NVarChar, picNode.Name);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertPicNode);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) picNode.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.PicNode picNode)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, picNode.Status);
            sql.AddParameter("@IsFile", SqlDbType.Bit, picNode.IsFile);
            sql.AddParameter("@Root", SqlDbType.NVarChar, picNode.Root);
            sql.AddParameter("@Path", SqlDbType.NVarChar, picNode.Path);
            sql.AddParameter("@Name", SqlDbType.NVarChar, picNode.Name);
            sql.AddParameter("@Id", SqlDbType.Int, picNode.Id);
            sql.ExecuteSql(SqlUpdatePicNode);
        }

        public void Delete(Core.Business.PicNode picNode)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, picNode.Id);
            sql.ExecuteSql(SqlDeletePicNode);
        }

        public IList<Core.Business.PicNode> GetAllPicNode()
        {
            IList<Core.Business.PicNode> picNodelist = new List<Core.Business.PicNode>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllPicNode);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.PicNode picNode = new Core.Business.PicNode();

                    if (!reader.IsDBNull(0)) picNode.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) picNode.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) picNode.IsFile = reader.GetBoolean(2);
                    if (!reader.IsDBNull(3)) picNode.Root = reader.GetString(3);
                    if (!reader.IsDBNull(4)) picNode.Path = reader.GetString(4);
                    if (!reader.IsDBNull(5)) picNode.Name = reader.GetString(5);

                    picNode.MarkOld();
                    picNodelist.Add(picNode);
                }
                reader.Close();
            }
            return picNodelist;
        }
        #endregion

        /// <summary>
        ///  获取节点的子节点
        /// </summary>
        /// <param name="nodeId"></param>
        /// <returns></returns>
        public ICollection<Core.Business.PicNode> GetSubs(int nodeId)
        {
            IList<Core.Business.PicNode> picNodelist = new List<Core.Business.PicNode>();

            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@nodeid", SqlDbType.Int, nodeId);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetSubs);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.PicNode picNode = new Core.Business.PicNode();

                    if (!reader.IsDBNull(0)) picNode.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) picNode.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) picNode.IsFile = reader.GetBoolean(2);
                    if (!reader.IsDBNull(3)) picNode.Root = reader.GetString(3);
                    if (!reader.IsDBNull(4)) picNode.Path = reader.GetString(4);
                    if (!reader.IsDBNull(5)) picNode.Name = reader.GetString(5);
                    picNode.MarkOld();
                    picNodelist.Add(picNode);
                }
                reader.Close();
            }
            return picNodelist;
        }



        /// <summary>
        /// 获取根节点
        /// </summary>
        /// <returns></returns>
        public PicNode GetRoot()
        {
            Core.Business.PicNode picNode = new Core.Business.PicNode();
            SqlServerUtility sql = new SqlServerUtility();
            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetRoot);

            if (reader != null)
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) picNode.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) picNode.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) picNode.IsFile = reader.GetBoolean(2);
                    if (!reader.IsDBNull(3)) picNode.Root = reader.GetString(3);
                    if (!reader.IsDBNull(4)) picNode.Path = reader.GetString(4);
                    if (!reader.IsDBNull(5)) picNode.Name = reader.GetString(5);

                    picNode.MarkOld();
                }
                reader.Close();
            }
            return picNode;
        }

        public ICollection<PicNode> GetRoots()
        {
            IList<Core.Business.PicNode> picNodelist = new List<Core.Business.PicNode>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetRoots);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.PicNode picNode = new Core.Business.PicNode();

                    if (!reader.IsDBNull(0)) picNode.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) picNode.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) picNode.IsFile = reader.GetBoolean(2);
                    if (!reader.IsDBNull(3)) picNode.Root = reader.GetString(3);
                    if (!reader.IsDBNull(4)) picNode.Path = reader.GetString(4);
                    if (!reader.IsDBNull(5)) picNode.Name = reader.GetString(5);

                    picNode.MarkOld();
                    picNodelist.Add(picNode);
                }
                reader.Close();
            }
            return picNodelist;
        }

        public void InsertNew(PicNode picNode)
        {
            if (picNode == null)
                throw new ArgumentNullException("argument is nulll!!! fuck you !!");

            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, picNode.Status);
            sql.AddParameter("@IsFile", SqlDbType.Bit, picNode.IsFile);
            sql.AddParameter("@Root", SqlDbType.NVarChar, picNode.Root);
            sql.AddParameter("@Path", SqlDbType.NVarChar, picNode.Path);
            sql.AddParameter("@Name", SqlDbType.NVarChar, picNode.Name);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertNew);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) picNode.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

        }





        public void DeleteDesc(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@id", SqlDbType.Int, id);

            sql.ExecuteSql(SqlDeleteDesc);
        }



        public FolderSearchModel Search(FolderSearch packet)
        {

            FolderSearchModel result = new FolderSearchModel();

            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@id", SqlDbType.Int, GetSqlValue(packet.Id > 0 ? (object)packet.Id : null));
            sql.AddParameter("@foldername", SqlDbType.NVarChar, GetSqlValue(packet.FolderName));
            sql.AddParameter("@keyword", SqlDbType.NVarChar, GetSqlValue(packet.KeyWord));
            sql.AddParameter("@photographer", SqlDbType.NVarChar, GetSqlValue(packet.Photographer));
            sql.AddParameter("@leader", SqlDbType.NVarChar, GetSqlValue(packet.Leader));
            sql.AddParameter("@otherleader", SqlDbType.NVarChar, GetSqlValue(packet.OtherLeader));
            sql.AddParameter("@guest", SqlDbType.NVarChar, GetSqlValue(packet.Guest));
            sql.AddParameter("@location", SqlDbType.NVarChar, GetSqlValue(packet.Location));
            sql.AddParameter("@eventinfo", SqlDbType.Int, GetSqlValue(packet.EventInfo > 0 ? (object)packet.EventInfo : null));
            sql.AddParameter("@eventdesc", SqlDbType.NVarChar, GetSqlValue(packet.EventInfoDesc));
            sql.AddParameter("@status", SqlDbType.Int, packet.Status != null ? (object)packet.Status.Value : (object)DBNull.Value);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSearch);

            IList<Folder> folders = new List<Folder>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.PicNode picNode = new Core.Business.PicNode();

                    if (!reader.IsDBNull(0)) picNode.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) picNode.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) picNode.IsFile = reader.GetBoolean(2);
                    if (!reader.IsDBNull(3)) picNode.Root = reader.GetString(3);
                    if (!reader.IsDBNull(4)) picNode.Path = reader.GetString(4);
                    if (!reader.IsDBNull(5)) picNode.Name = reader.GetString(5);

                    picNode.MarkOld();


                    FolderDesc folderDesc = new FolderDesc();

                    if (!reader.IsDBNull(6)) folderDesc.Id = reader.GetInt32(6);
                    if (!reader.IsDBNull(7)) folderDesc.EventInfo = reader.GetInt32(7);
                    if (!reader.IsDBNull(8)) folderDesc.StartDate = reader.GetDateTime(8);
                    if (!reader.IsDBNull(9)) folderDesc.EndDate = reader.GetDateTime(9);
                    if (!reader.IsDBNull(10)) folderDesc.KeyWord = reader.GetString(10);
                    if (!reader.IsDBNull(11)) folderDesc.Photographer = reader.GetString(11);
                    if (!reader.IsDBNull(12)) folderDesc.Leader = reader.GetString(12);
                    if (!reader.IsDBNull(13)) folderDesc.OtherLeader = reader.GetString(13);
                    if (!reader.IsDBNull(14)) folderDesc.Guest = reader.GetString(14);
                    if (!reader.IsDBNull(15)) folderDesc.InnerLocation = reader.GetString(15);
                    if (!reader.IsDBNull(16)) folderDesc.OuterLocation = reader.GetString(16);
                    if (!reader.IsDBNull(17)) folderDesc.EventDesc = reader.GetString(17);

                    folders.Add(new Folder(picNode, folderDesc));
                }
                reader.Close();
            }

            result.Folders = folders;
            result.Search = packet;

            return result;
        }

        public static object GetSqlValue(object value)
        {
            if (value != null)
            {
                return value;
            }
            else
            {
                return DBNull.Value;
            }
        }


        public PicNode FindByPath(string path)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Path", SqlDbType.NVarChar, path);

            sql.AddParameter("@Status", SqlDbType.Int, 0);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlFindByPath);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.PicNode picNode = new Core.Business.PicNode();

                if (!reader.IsDBNull(0)) picNode.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) picNode.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) picNode.IsFile = reader.GetBoolean(2);
                if (!reader.IsDBNull(3)) picNode.Root = reader.GetString(3);
                if (!reader.IsDBNull(4)) picNode.Path = reader.GetString(4);
                if (!reader.IsDBNull(5)) picNode.Name = reader.GetString(5);

                reader.Close();
                return picNode;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }
    }
}
